/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package proyecto1.Manager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;
import proyecto1.GestionDB;
import proyecto1.TO.AsistenciaTO;

/**
 *
 * @author alex
 */
public class AsistenciaManager {

    GestionDB cnn = new GestionDB();

    public List<AsistenciaTO> listar() throws Exception {
        ArrayList data = new ArrayList();
        String sql = "SELECT * FROM asistencia";

        cnn.conectar();
        ResultSet rs = cnn.ejecutarConsulta(sql);//Consulta a la base de datos y lo almacena en "rs"
        while (rs.next()) {

            AsistenciaTO t = new AsistenciaTO();

            t.setID_asistencia(rs.getString("ID_asistencia"));
            t.setHora_asistencia(rs.getString("Hora_asistencia"));
            t.setDNI(rs.getString("DNI"));
            t.setID_reunion(rs.getString("ID_reunion"));
            data.add(t);

        }
        rs.close();
        cnn.desconectar();
        return data;
    }

    public void Agregar(AsistenciaTO data) throws Exception {

        String sql = "INSERT INTO asistencia(ID_asistencia,Hora_asistencia,DNI,ID_reunion) VALUES (?,?,?,?)";
        Connection c = null;
        try {
            c = cnn.conectar();
            c.setAutoCommit(false);
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setString(1, data.getID_asistencia());
            ps.setString(2, data.getHora_asistencia());
            ps.setString(3, data.getDNI());
            ps.setString(4, data.getID_reunion());
            ps.executeUpdate();
            c.commit();
            ps.close();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
            c.rollback();
        } finally {
            cnn.desconectar();
        }
    }

    public AsistenciaTO buscaPorid(Object id) throws Exception {
        AsistenciaTO t = null;
        String sql = "SELECT * FROM asistencia WHERE ID_asistencia=" + id;
        //id_persona,cod_pe,nom_pe,dni_pe,dir_pe,cel_pe,ema_pe,fecnac_pe
        try {
            cnn.conectar();
            ResultSet rs = cnn.ejecutarConsulta(sql);
            if (rs.next()) {
                t = new AsistenciaTO();
                t.setID_asistencia(rs.getString("ID_asistencia"));
                t.setHora_asistencia(rs.getString("Hora_asistencia"));
                t.setDNI(rs.getString("DNI"));
                t.setID_reunion(rs.getString("ID_reunion"));


            }
            rs.close();
            cnn.desconectar();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }

        return t;
    }

//________________________editar 
    public void Editar(AsistenciaTO data) throws Exception {
        String sql = "UPDATE asistencia SET Hora_asistencia=?,DNI=?,ID_reunion=? WHERE ID_asistencia=?";
        Connection c = null;
        //id_persona,cod_pe,nom_pe,dni_pe,dir_pe,cel_pe,ema_pe,fecnac_pe
        try {
            c = cnn.conectar();
            c.setAutoCommit(false);
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setString(4, data.getID_asistencia());
            ps.setString(1, data.getHora_asistencia());
            ps.setString(2, data.getDNI());
            ps.setString(3, data.getID_reunion());
            ps.executeUpdate();
            c.commit();
            ps.close();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
            c.rollback();
        } finally {
            cnn.desconectar();
        }

    }

    //----------------------------------Eliminar
    public void eliminar(String id) throws Exception {
        try {
            Connection c = cnn.conectar();
            String sql = "DELETE FROM asistencia WHERE ID_asistencia=?";
            PreparedStatement ps = c.prepareStatement(sql);
            ps.setString(1, id);
            ps.executeUpdate();
            ps.close();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        } finally {
            cnn.desconectar();
        }

    }

    public List<AsistenciaTO> buscaPorDescripcion(Object des) throws Exception {
        ArrayList data = new ArrayList();
        AsistenciaTO t = null;
        String sql = "SELECT * FROM asistencia WHERE DNI LIKE '" + des + "%'";
        try {
            cnn.conectar();
            ResultSet rs = cnn.ejecutarConsulta(sql);
            while (rs.next()) {
                t = new AsistenciaTO();
                t.setID_asistencia(rs.getString("ID_asistencia"));
                t.setHora_asistencia(rs.getString("Hora_asistencia"));
                t.setDNI(rs.getString("DNI"));
                t.setID_reunion(rs.getString("ID_reunion"));
                data.add(t);
            }
            rs.close();
            cnn.desconectar();

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.getMessage());
        }

        return data;
    }
}
